﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Windows.Forms;

namespace TooltracePC
{
    class dbSync : clsDatabase
    {
        private static int _nCount = 0;


        public int getCount()
        {
            if (_nCount > 0)
            {
                return _nCount;
            }
            setCount();
            return _nCount;
        }

        private void setCount()
        {
            string strSql = @"select count(*) AS c from Sync";
            DataTable dt = null;
            dt = SelectToDataTable(strSql);
            _nCount = (int)dt.Rows[0]["c"];
        }



        //get All
        public DataTable getAll()
        {
            return getByManAndDate("", "", "");
        }


        //get
        public DataTable getByFileName(string strName)
        {
            string strSql = @"select * from Sync where FILENAME = '" + strName + "'";
            return SelectToDataTable(strSql);
        }

        //get
        public DataTable getByManAndDate(string man, string sdate, string edate)
        {
            string strSql = @"select CREATEMAN, CREATEDATE, CREATETIME, FILENAME, FILESTATUS from Sync where 1 = 1 ";
            if (!string.IsNullOrEmpty(man.Trim()))
            {
                if(man.Trim() != "所有人")
                {
                    strSql += " AND CREATEMAN Like '%" + man.Trim() + "%' ";
                }
                
            }

            if (!string.IsNullOrEmpty(sdate.Trim()))
            {
                strSql += " AND CREATEDATE >= '" + sdate.Trim() + "' ";
            }

            if (!string.IsNullOrEmpty(edate.Trim()))
            {
                strSql += " AND CREATEDATE <= '" + edate.Trim() + "' ";
            }

            if(string.IsNullOrEmpty(sdate.Trim()) && string.IsNullOrEmpty(edate.Trim()))
            {
                if (string.IsNullOrEmpty(man.Trim()) || man.Trim() == "所有人")
                {
                    strSql = @"select top 300 CREATEMAN, CREATEDATE, CREATETIME, FILENAME, FILESTATUS  from Sync order by CREATEDATE desc";
                }
            }
  

            string stStr = "";
            string strColor = "GREEN";
            //string sql;
            DataTable dt = null;
            dt = SelectToDataTable(strSql);

            DataTable rDt = new DataTable();
            rDt.Columns.Add("CREATEMAN", Type.GetType("System.String"));
            rDt.Columns.Add("CREATEDATE", Type.GetType("System.String"));
            rDt.Columns.Add("CREATETIME", Type.GetType("System.String"));
            rDt.Columns.Add("FILESTATUS", Type.GetType("System.String"));
            rDt.Columns.Add("FILENAME", Type.GetType("System.String"));
            rDt.Columns.Add("STATUSCOLOR", Type.GetType("System.String"));

            int ns = 0;
            int j;
            int outCount = 0;
            int c1Count = 0;
            int c1MoreCount = 0;
            int c1LessCount = 0;

            int c2Count = 0;
            int c2MoreCount = 0;
            int c2LessCount = 0;

            int inCount = 0;
            int inMoreCount = 0;
            int inLessCount = 0;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                ns = int.Parse(dt.Rows[i]["FILESTATUS"].ToString());
                DataTable syncDataDb = new DataTable();

                DataRow dr = rDt.NewRow();
                dr["CREATEMAN"] = dt.Rows[i]["CREATEMAN"].ToString();
                dr["CREATEDATE"] = dt.Rows[i]["CREATEDATE"].ToString();
                dr["CREATETIME"] = dt.Rows[i]["CREATETIME"].ToString();
                if (ns == 1)
                {
                    stStr = "当前:出库";
                }
                else if (ns == 2)
                {
                    stStr = "当前:清点一";
                }
                else if (ns == 3)
                {
                    stStr = "当前:清点二";
                }
                else if (ns == 4)
                {
                    stStr = "当前:入库";
                }
                else if (ns == 5)
                {
                    stStr = "当前:已完成";
                }
                //查找
                syncDataDb = getDataByName(dt.Rows[i]["FILENAME"].ToString());
                if (syncDataDb.Rows.Count > 0)
                {
                    for (j = 0; j < syncDataDb.Rows.Count; j++)
                    {
                        if (syncDataDb.Rows[j]["出库"].ToString().Trim() == "1")
                        {
                            //出库记录
                            outCount++;
                        }

                        //c1
                        if (syncDataDb.Rows[j]["出库"].ToString() == "1" && syncDataDb.Rows[j]["清点一"].ToString() == "1")
                        {
                            c1Count++;
                        }
                        else if (syncDataDb.Rows[j]["出库"].ToString() != "1" && syncDataDb.Rows[j]["清点一"].ToString() == "1")
                        {
                            c1MoreCount++;
                        }
                        else if (syncDataDb.Rows[j]["出库"].ToString() == "1" && syncDataDb.Rows[j]["清点一"].ToString() != "1")
                        {
                            c1LessCount++;
                        }

                        //c2
                        if (syncDataDb.Rows[j]["出库"].ToString() == "1" && syncDataDb.Rows[j]["清点二"].ToString() == "1")
                        {
                            c2Count++;
                        }
                        else if (syncDataDb.Rows[j]["出库"].ToString() != "1" && syncDataDb.Rows[j]["清点二"].ToString() == "1")
                        {
                            c2MoreCount++;
                        }
                        else if (syncDataDb.Rows[j]["出库"].ToString() == "1" && syncDataDb.Rows[j]["清点二"].ToString() != "1")
                        {
                            c2LessCount++;
                        }


                        if (syncDataDb.Rows[j]["出库"].ToString() == "1" && syncDataDb.Rows[j]["入库"].ToString() == "1")
                        {
                            inCount++;
                        }
                        else if (syncDataDb.Rows[j]["出库"].ToString() != "1" && syncDataDb.Rows[j]["入库"].ToString() == "1")
                        {
                            inMoreCount++;
                        }
                        else if (syncDataDb.Rows[j]["出库"].ToString() == "1" && syncDataDb.Rows[j]["入库"].ToString() != "1")
                        {
                            inLessCount++;
                        }
                    }

                    stStr += ".出库: " + outCount + " 条";
                    //c1
                    if (c1MoreCount == 0 && c1LessCount == 0)
                    {
                        stStr += ",清点1一致.";
                    }
                    else if (c1MoreCount > 0 && c1LessCount == 0)
                    {
                        stStr += ",清点1多出: " + c1MoreCount + " 条.";
                    }
                    else if (c1MoreCount == 0 && c1LessCount > 0)
                    {
                        if (c1LessCount != outCount)
                        {
                            stStr += ",清点1缺少: " + c1LessCount + " 条.";
                        }
                    }
                    else
                    {
                        stStr += ",清点1多出: " + c1MoreCount + " 条.缺少: " + c1LessCount + " 条.";
                    }


                    //c2
                    if (c2MoreCount == 0 && c2LessCount == 0)
                    {
                        stStr += "清点2一致.";
                    }
                    else if (c2MoreCount > 0 && c2LessCount == 0)
                    {
                        stStr += "清点2多出: " + c2MoreCount + " 条.";
                    }
                    else if (c2MoreCount == 0 && c2LessCount > 0)
                    {
                        if (c2LessCount != outCount)
                        {
                            stStr += "清点2缺少: " + c2LessCount + " 条.";
                        }
                    }
                    else
                    {
                        stStr += "清点2多出: " + c2MoreCount + " 条.缺少: " + c2LessCount + " 条.";
                    }

                    //in
                    if (inMoreCount == 0 && inLessCount == 0)
                    {
                        stStr += "入库一致.";
                        //更新文件状态
                        /*if(ns == 4)
                        {
                            sql = "update Sync set FILESTATUS = 5 where FILENAME = '" + dt.Rows[i]["FILENAME"].ToString() + "'";
                            execNonSQL(sql);
                        }*/
                    }
                    else if (inMoreCount > 0 && inLessCount == 0)
                    {
                        stStr += "入库多出: " + inMoreCount + " 条.";
                    }
                    else if (inMoreCount == 0 && inLessCount > 0)
                    {
                        if (inLessCount != outCount)
                        {
                            stStr += "入库缺少: " + inLessCount + " 条.";
                        }
                    }
                    else
                    {
                        stStr += "入库多出: " + inMoreCount + " 条.缺少: " + inLessCount + " 条.";
                    }

                    if (c1MoreCount + c2MoreCount + inMoreCount > 0 && c1LessCount + c2LessCount + inLessCount == 0)
                    {
                        strColor = "YELLOW";
                    }
                    else if (c1MoreCount + c2MoreCount + inMoreCount == 0 && c1LessCount + c2LessCount + inLessCount > 0)
                    {
                        strColor = "RED";
                    }
                    else if (c1MoreCount + c2MoreCount + inMoreCount > 0 && c1LessCount + c2LessCount + inLessCount > 0)
                    {
                        strColor = "CYAN";
                    }
                    else
                    {
                        strColor = "GREEN";
                    }


                    outCount = 0;

                    c1Count = 0;
                    c1MoreCount = 0;
                    c1LessCount = 0;

                    c2Count = 0;
                    c2MoreCount = 0;
                    c2LessCount = 0;

                    inCount = 0;
                    inMoreCount = 0;
                    inLessCount = 0;

                }
                dr["STATUSCOLOR"] = strColor;
                dr["FILESTATUS"] = stStr;
                dr["FILENAME"] = dt.Rows[i]["FILENAME"].ToString();
                rDt.Rows.Add(dr);
            }
            return rDt;
        }

        //get
        public DataTable getSyncDataByName(string strName)
        {
            string strSql = @"select * from SyncData where FILENAME = '" + strName + "'";
            DataTable dt = null;
            dt = SelectToDataTable(strSql);

            DataTable rDt = new DataTable();
            rDt.Columns.Add("标签ID", Type.GetType("System.String"));
            rDt.Columns.Add("名称", Type.GetType("System.String"));
            rDt.Columns.Add("编码", Type.GetType("System.String"));
            rDt.Columns.Add("出库人", Type.GetType("System.String"));
            rDt.Columns.Add("出库时间", Type.GetType("System.String"));
            rDt.Columns.Add("清点一人", Type.GetType("System.String"));
            rDt.Columns.Add("清点一时间", Type.GetType("System.String"));
            rDt.Columns.Add("清点二人", Type.GetType("System.String"));
            rDt.Columns.Add("清点二时间", Type.GetType("System.String"));
            rDt.Columns.Add("入库人", Type.GetType("System.String"));
            rDt.Columns.Add("入库时间", Type.GetType("System.String"));
            rDt.Columns.Add("备注", Type.GetType("System.String"));
            rDt.Columns.Add("C", Type.GetType("System.String"));

            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DataRow dr = rDt.NewRow();
                    dr["标签ID"] = dt.Rows[i]["标签ID"].ToString();
                    dr["名称"] = dt.Rows[i]["名称"].ToString();
                    dr["编码"] = dt.Rows[i]["编码"].ToString();
                    dr["出库人"] = dt.Rows[i]["出库人姓名"].ToString();
                    dr["出库时间"] = dt.Rows[i]["出库时间"].ToString();
                    dr["清点一人"] = dt.Rows[i]["清点一姓名"].ToString();
                    dr["清点一时间"] = dt.Rows[i]["清点一时间"].ToString();
                    dr["清点二人"] = dt.Rows[i]["清点二姓名"].ToString();
                    dr["清点二时间"] = dt.Rows[i]["清点二时间"].ToString();
                    dr["入库人"] = dt.Rows[i]["入库人姓名"].ToString();
                    dr["入库时间"] = dt.Rows[i]["入库时间"].ToString();
                    dr["备注"] = dt.Rows[i]["备注"].ToString();

                    if (dt.Rows[i]["出库"].ToString() == "1")
                    {
                        if (dt.Rows[i]["清点一"].ToString() == "1" && dt.Rows[i]["清点二"].ToString() == "1" && dt.Rows[i]["入库"].ToString() == "1")
                        {
                            dr["C"] = "G";
                        }
                        else
                        {
                            dr["C"] = "R";
                        }
                    }
                    else
                    {
                        dr["C"] = "Y";
                    }

                    rDt.Rows.Add(dr);
                }
            }

            return rDt;
        }
        
        
        //get by name
        public DataTable getDataByName(string strName)
        {
            string strSql = @"select * from SyncData where FILENAME = '" + strName + "'";
            return SelectToDataTable(strSql);
        }

        //get by ID

        public DataTable getByID(int nID)
        {

            string strSql = @"select * from Tool where ID = " + nID;
            DataTable dtTool = null;
            dtTool = SelectToDataTable(strSql);
            return dtTool;
        }


        //insert sync data
        public int insertSyncData(string FILENAME, string LableID, string LableName, string LableNo, string OutFlag, string OutMan, string OutTime, string C1Flag, string C1Man, string C1Time, string C2Flag, string C2Man, string C2Time, string InFlag, string InMan, string InTime, string md, string note)
        {
            string strSQL;
            if (string.IsNullOrEmpty(FILENAME) || string.IsNullOrEmpty(LableID) || string.IsNullOrEmpty(LableName))
            {
                //MessageBox.Show("必填项不能为空", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return 0;
            }

            strSQL = "insert into SyncData(FILENAME, 标签ID, 名称, 编码, 出库, 出库人姓名, 出库时间, 清点一, 清点一姓名, 清点一时间, 清点二, 清点二姓名, 清点二时间, 入库, 入库人姓名, 入库时间, 手动, 备注) values('" + FILENAME + "', '" + LableID + "', '" + LableName + "', '" + LableNo + "', '"+ OutFlag + "', '"+ OutMan + "', '"+ OutTime + "', '"+ C1Flag + "', '"+ C1Man + "', '"+ C1Time + "', '"+ C2Flag + "','"+ C2Man + "', '"+ C2Time + "', '"+ InFlag + "', '"+ InMan + "', '"+ InTime + "', '"+ md +"','"+ note +"')";
            //MessageBox.Show(strSQL, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
            execNonSQL(strSQL);
            return 1;

        }


        //inster
        public int insertDb(int ID, string CREATEMAN, string CREATEDATE, string CREATETIME, string FILENAME, int FILESTATUS, string FILESTAMP)
        {
            string strSQL;

            if (string.IsNullOrEmpty(FILENAME) || string.IsNullOrEmpty(CREATEMAN))
            {
                //MessageBox.Show("必填项不能为空", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return 0;
            }

            strSQL = "insert into Sync(ID, CREATEMAN, CREATEDATE, CREATETIME, FILENAME, FILESTATUS, FILESTAMP) values(" + ID + ", '" + CREATEMAN + "', '" + CREATEDATE + "', '"+ CREATETIME + "', '"+ FILENAME + "', "+ FILESTATUS + ", '"+ FILESTAMP + "')";
            execNonSQL(strSQL);
            return 1;
        }


        //update
        public int updateSync(string FILENAME, int FILESTATUS, string FILESTAMP)
        {
            string strSQL;

            if (string.IsNullOrEmpty(FILENAME) || string.IsNullOrEmpty(FILESTAMP))
            {
                //MessageBox.Show("必填项不能为空", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return 0;
            }

          
            strSQL = "update Sync set FILESTATUS = " + FILESTATUS + ", FILESTAMP = '" + FILESTAMP + "' where FILENAME = '" + FILENAME + "'";
            execNonSQL(strSQL);
            return 1;
        }

        //
        public int delSyncData(string fileName)
        {
            if (string.IsNullOrEmpty(fileName))
            {
                return 0;
            }
            string strSQL = "delete from SyncData where FILENAME = '"+ fileName + "'";
            execNonSQL(strSQL);
            return 1;
        }


        //del more 
        public int delSyncDataMore(string fileName)
        {
            if (string.IsNullOrEmpty(fileName))
            {
                return 0;
            }

            string strSQL = "delete from SyncData where FILENAME = '" + fileName + "' AND 出库 = ''";
            execNonSQL(strSQL);
            return 1;
        }

        //update sync data
        public int mtUpSyncData(string fileName, string strMan, string strDate)
        {
            if (string.IsNullOrEmpty(fileName))
            {
                return 0;
            }

            string sql = "";
            DataTable dt = new DataTable();
            dt = getDataByName(fileName);
            if(dt.Rows.Count == 0)
            {
                return 0;
            }

            for(int i = 0; i < dt.Rows.Count; i++)
            {
                sql = "";
                if (string.IsNullOrEmpty(dt.Rows[i]["清点一"].ToString().Trim()))
                {
                    sql = "update SyncData set 清点一 = '1', 清点一姓名 = '"+ strMan + "', 清点一时间 = '"+ strDate + "', 备注 = '手动完成' where FILENAME = '" + fileName + "'";
                    execNonSQL(sql);
                }
                if (string.IsNullOrEmpty(dt.Rows[i]["清点二"].ToString().Trim()))
                {
                    sql = "update SyncData set 清点二 = '1', 清点二姓名 = '" + strMan + "', 清点二时间 = '" + strDate + "', 备注 = '手动完成' where FILENAME = '" + fileName + "'";
                    execNonSQL(sql);
                }
                if (string.IsNullOrEmpty(dt.Rows[i]["入库"].ToString().Trim()))
                {
                    sql = "update SyncData set 入库 = '1', 入库人姓名 = '" + strMan + "', 入库时间 = '" + strDate + "', 备注 = '手动完成' where FILENAME = '" + fileName + "'";
                    execNonSQL(sql);
                }
                //execNonSQL(sql);
            }

            //更新sync
            sql = "update Sync set FILESTATUS = 5 where FILENAME = '"+ fileName + "'";
            execNonSQL(sql);

            return 1;

        }

        


    }
}
